package com.cloud.hotline.common.utils;

import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.ReflectionUtils;

import com.alibaba.fastjson.JSON;

public class ExcelHelper {
	private static final Logger logger = LoggerFactory.getLogger(ExcelHelper.class); 
	/**
	 * 解析XLS数据行为对象
	 * 
	 * @param in
	 * @param columnNames
	 * @param clazz
	 * @return
	 */
	public static <T> List<T> xlsx2List(InputStream in, String[] columnNames, Class<T> clazz) {
		return xlsx2List(in, columnNames, clazz, 0, 1);
	}

	/**
	 * 解析XLS数据行为对象
	 * 
	 * @param in
	 *            输入流
	 * @param columnNames
	 *            列名
	 * @param sheetIdx
	 *            sheet索引
	 * @param startRow
	 *            开始行
	 * @return
	 */
	public static <T> List<T> xlsx2List(InputStream in, String[] columnNames, Class<T> clazz, int sheetIdx,
			int startRow) {
		Workbook wk = null;
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;
		try {
			List<Map<String, String>> rowDatas = new ArrayList<Map<String, String>>();
			wk = new XSSFWorkbook(in);
			sheet = wk.getSheetAt(sheetIdx);
			for (int r = startRow; r <= sheet.getLastRowNum(); r++) {
				row = sheet.getRow(r);
				if (null != row) {
					Map<String, String> rowData = new HashMap<String, String>();
					for (int i = 0; i <= row.getLastCellNum(); i++) {
						cell = row.getCell(i);
						if (null == cell)
							continue;
						String content = null;
						// 时间格式
						if (cell.getCellTypeEnum() == CellType.NUMERIC) {
							short format = cell.getCellStyle().getDataFormat();
							SimpleDateFormat sdf = null;
							if (format == 14 || format == 31 || format == 57 || format == 58
									|| (176 <= format && format <= 178) || (182 <= format && format <= 196)
									|| (210 <= format && format <= 213) || (208 == format)) { // 日期
								sdf = new SimpleDateFormat("yyyyMMdd");
							} else if (format == 20 || format == 32 || format == 183
									|| (200 <= format && format <= 209)) { // 时间
								sdf = new SimpleDateFormat("HH:mm");
							} else if(format == 22){
								sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
							}
							if (null != sdf) {
								double value = cell.getNumericCellValue();
								Date date = DateUtil.getJavaDate(value);
								if (null != date) {
									content = sdf.format(date);
								}
							} else {
								cell.setCellType(CellType.STRING);
								content = cell.getStringCellValue();
							}
						} else {
							cell.setCellType(CellType.STRING);
							content = cell.getStringCellValue();
						}
						
						if (!StringUtils.isEmpty(content) && !"#N/A".equals(content)) {
							rowData.put(columnNames[i], content);
						}
					}
					if (rowData.size() > 0) {
						rowDatas.add(rowData);
					}
				}
			}
			return JSON.parseArray(JSON.toJSONString(rowDatas), clazz);
		} catch (Exception e) {
			logger.error("excel转换List对象失败", e);
			throw new RuntimeException(e);
		} finally {
			try {
				if (null != wk) {
					wk.close();
					wk = null;
				}
				if (null != in) {
					in.close();
					in = null;
				}
			} catch (Exception e2) {
			}
		}
	}

	/**
	 * 数据对象转换为excel
	 * 
	 * @param obj
	 * @param title
	 * @param header
	 * @param columns
	 * @return
	 */
	public static OutputStream list2Xlsx(List<?> objs, String title, String subTitle, ExcelField[] excelFields,
			Class<?> clazz) {
		Workbook wb = null;
		Sheet sheet = null;
		try {
			wb = new XSSFWorkbook();
			sheet = wb.createSheet(title);
			for (int col = 0; col < excelFields.length; col++) {
				ExcelField field = excelFields[col];
				if (0 == field.getWidth()) {
					sheet.setColumnWidth(col, field.getHeader().length() * 2000);
				} else {
					sheet.setColumnWidth(col, field.getWidth() * 2000);
				}
			}

			int i = 0;
			// 标题
			if (!StringUtils.isEmpty(title)) {
				CellRangeAddress titleRange = new CellRangeAddress(i, i, 0, excelFields.length - 1);
				sheet.addMergedRegion(titleRange);
				Row titleRow = sheet.createRow(i++);
				Cell titleCell = titleRow.createCell(0);
				titleCell.setCellValue(title);
				titleCell.setCellStyle(generateCellStyle(wb, 1, false));
			}
			// 副标题
			if (!StringUtils.isEmpty(subTitle)) {
				CellRangeAddress subTitleRange = new CellRangeAddress(i, i, 0, excelFields.length - 1);
				sheet.addMergedRegion(subTitleRange);
				Row subTitleRow = sheet.createRow(i++);
				subTitleRow.createCell(0).setCellValue(subTitle);
				subTitleRow.getCell(0).setCellStyle(generateCellStyle(wb, 2, false));
			}
			// 表头
			Row titleRow = sheet.createRow(i++);
			CellStyle rqCellStyle = generateCellStyle(wb, 4, true);
			for (int idx = 0; idx < excelFields.length; idx++) {
				ExcelField field = excelFields[idx];
				Cell cell = titleRow.createCell(idx);
				cell.setCellValue(field.getHeader());
				cell.setCellStyle(rqCellStyle);
			}
			titleRow.setHeight((short) 450);

			// 数据内容
			CellStyle dataStyle = generateCellStyle(wb, 8, true);
			for (Object obj : objs) {
				Row dataRow = sheet.createRow(i++);
				for (int col = 0; col < excelFields.length; col++) {
					ExcelField xlsField = excelFields[col];
					Cell cell = dataRow.createCell(col);
					Field field = ReflectionUtils.findField(clazz, xlsField.getField());
					field.setAccessible(true);
					Object value = ReflectionUtils.getField(field, obj);
					cell.setCellValue(getValue(value, xlsField));
					cell.setCellStyle(dataStyle);
				}
			}
			OutputStream bos = new ByteArrayOutputStream();
			wb.write(bos);
			return bos;
		} catch (Exception e) {
			logger.error("List对象转换excel失败", e);
			throw new RuntimeException(e);
		} finally {
			try {
				if (null != wb) {
					wb.close();
					wb = null;
				}
			} catch (Exception e2) {
			}
		}
	}

	private static String getValue(Object obj, ExcelField field) {
		if(null == obj)return "";
		if (StringUtils.isEmpty(field.getFormat()) || null == obj) {
			return obj + "";
		}
		switch (field.getDateType()) {
		case ExcelField.DATETYPE_NUMBER:
			return new DecimalFormat(field.getFormat()).format(obj);
		case ExcelField.DATETYPE_DATETIME:
			return DateHelper.dateToFormatStr((Date) obj, field.getFormat());
		default:
			return new DecimalFormat(field.getFormat()).format(obj);
		}
	}

	private static CellStyle generateCellStyle(Workbook wb, int type, boolean needBorder) {
		CellStyle style = wb.createCellStyle();
		if (needBorder) {
			style.setVerticalAlignment(VerticalAlignment.CENTER);
			style.setBorderBottom(BorderStyle.THIN);
			style.setBorderLeft(BorderStyle.THIN);// 左边框
			style.setBorderTop(BorderStyle.THIN);// 上边框
			style.setBorderRight(BorderStyle.THIN);// 右边框
		}
		Font font = wb.createFont();
		switch (type) {
		case 1: // "TITLE"
			style.setAlignment(HorizontalAlignment.CENTER);
			style.setVerticalAlignment(VerticalAlignment.CENTER);
			font.setFontName("仿宋_GB2312");
			font.setFontHeightInPoints((short) 22);
			font.setBold(true);
			style.setFont(font);
			return style;
		case 2:// "SUBTITLE"
			style.setAlignment(HorizontalAlignment.RIGHT);
			font.setFontName("仿宋_GB2312");
			font.setFontHeightInPoints((short) 12);
			style.setFont(font);
			font.setBold(true);
			style.setWrapText(true);
			return style;
		case 3:// "SEQ"
			style.setAlignment(HorizontalAlignment.CENTER);
			font.setFontName("仿宋_GB2312");
			font.setFontHeightInPoints((short) 12);
			font.setBold(true);
			style.setFont(font);
			return style;
		case 4:// "NAME"
			style.setAlignment(HorizontalAlignment.CENTER);
			font.setFontName("仿宋_GB2312");
			font.setFontHeightInPoints((short) 12);
			font.setBold(true);
			style.setFont(font);
			return style;
		case 5:// "ATTENDANCE"
			style.setAlignment(HorizontalAlignment.CENTER);
			font.setFontName("仿宋_GB2312");
			font.setFontHeightInPoints((short) 12);
			font.setBold(true);
			style.setFont(font);
			return style;
		case 6:// "FOOTER"
			style.setVerticalAlignment(VerticalAlignment.CENTER);
			style.setAlignment(HorizontalAlignment.CENTER);
			font.setFontName("Times New Roman");
			font.setFontHeightInPoints((short) 12);
			font.setBold(true);
			style.setFont(font);
			return style;
		case 7:// "CONTENTHOUR"
			style.setVerticalAlignment(VerticalAlignment.CENTER);
			style.setAlignment(HorizontalAlignment.CENTER);
			font.setBold(true);
			font.setFontName("Times New Roman");
			font.setFontHeightInPoints((short) 14);
			style.setFont(font);
			return style;
		case 8:// "CONTENTNAME"
			style.setVerticalAlignment(VerticalAlignment.CENTER);
			style.setAlignment(HorizontalAlignment.CENTER);
			font.setFontHeightInPoints((short) 10.5);
			return style;
		case 9:// "CONTENTSEQ"
			style.setVerticalAlignment(VerticalAlignment.CENTER);
			style.setAlignment(HorizontalAlignment.CENTER);
			font.setBold(true);
			font.setFontName("Times New Roman");
			font.setFontHeightInPoints((short) 10.5);
			style.setFont(font);
			return style;
		case 10:// "DAYHEADER"
			style.setAlignment(HorizontalAlignment.CENTER);
			style.setWrapText(true);
			font.setFontHeightInPoints((short) 10);
			style.setFont(font);
			return style;
		default:
			return style;
		}
	}
}
